'''
Author: thinktanker
Date: 2024-02-06 17:21:27
LastEditors: duliang thinktanker@163.com
LastEditTime: 2025-01-26 10:23:13
Description: 这里实现各种查询
'''
import sqlite3
import time
import locale
import requests
import socket
import re

server_ip = 'pic.u96242.nyat.appc'  # 或者替换为服务器的实际IP地址
server_port = 14861  # 替换为你的服务器端口
locale.setlocale(locale.LC_CTYPE, 'chinese')

db_path = r'F:\gitee\control-net\data\hisdata.db'
db2_path = r'F:\gitee\control-net\data\hisdata2.db'
dbsw_path = r'D:\lljdata\swdb.db'
ename_list = ["35kV", "zhb", "sb", "6kV", "zb", "1", "2", "3", "4"]
realname_list = [
    "35kV", "主变", "所变", "6 kV", "站变", "1#  ", "2#  ", "3#  ", "4#  "
]

realname_list2 = {
    '1': '1#',
    '2': '2#',
    "3": "3#",
    '4': '4#',
    'zhbg': '站变高压侧',
    'zhbd': '站变低压侧',
    'zb': '主变',
    '35kV': '35kV',
}


def send_data_to_server(server_ip, server_port, message):
    """
    向指定的TCP服务器发送数据。
    
    :param server_ip: 服务器的IP地址
    :param server_port: 服务器的端口号
    :param message: 要发送的数据
    """
    # 创建一个socket对象
    client_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    client_socket.settimeout(10)
    response = b''
    while 1:
        try:
            # 连接到服务器
            client_socket.connect((server_ip, server_port))

            # 发送数据
            client_socket.sendall(message.encode('utf-8'))
            print(f"Sent message to server: {message}")

            # 可以选择接收服务器的响应
            response = client_socket.recv(1024)
            print(f"Server response: {response.decode('utf-8')}")

            break
        except Exception as e:
            print(f"An error occurred: {e}")
            time.sleep(1)

    client_socket.close()
    return response.decode('utf-8')


def getsq(gcname, gcstart, gcend):
    dbpath = 'F:\lljdata\swdb.db'
    print(dbpath)
    conn = sqlite3.connect(dbpath)
    c = conn.cursor()
    sq = ""
    if gcstart != "":
        tmps = f"{gcstart[0:4]}-{gcstart[4:6]}-{gcstart[6:8]}"
        tmpe = tmps
        gcstart = f"{tmps} 00:00:00"
        if gcend == "":
            gcend = f"{tmpe} 23:59:59"
        else:
            tmpe = f"{gcend[0:4]}-{gcend[4:6]}-{gcend[6:8]}"
            gcend = f"{tmpe} 23:59:59"

        # sqltxt = f"select name,sysw,xysw,time from swdb where swbdb.name like %'{gcname}'% order by time desc limit 4"
        sqltxt = f"""select name,sysw,xysw,time from swdb where name like "%{gcname}%" and time between "{gcstart}" and "{gcend}" order by time desc limit 4"""
    else:
        sqltxt = f"""select name,sysw,xysw,time from swdb where name like "%{gcname}%" order by time desc limit 4"""
    print(sqltxt)
    rs = c.execute(sqltxt)
    for row in rs:
        sq += (str(row[0]) + '\n' + str(row[3]) + "\n上游：" + str(row[1]) +
               " 下游：" + str(row[2]) + "\n\n")
    c.close()
    conn.close()
    return sq


def getnewsq(gcname):
    url = "http://127.0.0.1:8211/sq?gcname=" + gcname
    # url = "https://58.220.44.15:14861/sq?gcname=" + gcname
    while 1:
        try:
            resp = requests.get(url)
            sq = ''
            if resp.status_code == 200:
                sq = resp.json()['sq']
            return sq
        except Exception as e:
            print(f"An error occurred: {e}")
            time.sleep(1)


def getzhakg(zhaDetail,
             liuliang=0.0,
             zhakong=0.0,
             zhakuan=0.0,
             xishu=0.0,
             xishuE=0.0):
    """
    参数：
    
    zhaDetail: 闸信息
    
    liuliang: 流量
    
    zhakong: 开闸孔
    
    zhakuan: 闸宽
    
    xishu,xishuE: 系数  
    
      
    返回：获取闸门开高
    """
    result = ""
    # 类型强制转换
    try:
        liuliang = float(liuliang)
        zhakong = float(zhakong)
        zhakuan = float(zhakuan)
        xishu = float(xishu)
        xishuE = float(xishuE)
    except Exception as e:
        print(e)
        return "参数错误"
    # 刘老涧新闸公式 POWER(F4/(2.72*C4*E4^0.5),1/1.55)

    # 刘老涧闸公式

    # 默认返回，获取水位信息
    swStr = "错误"
    kg_e = 0
    # 上下游水位
    sysw = None
    xysw = None
    gc_name = None
    if "新" in zhaDetail:
        swStr = getnewsq("刘涧新").split("\n\n")[0]
        gc_name = "新闸"
    else:
        swStr = getnewsq("老闸").split("\n\n")[0]
    result += swStr + '\n'
    result += f"流量：{liuliang }\n"
    result += f"开孔：{zhakong}\n"
    sw_list = re.findall(r"上游：(\d+.\d+) 下游：(\d+.\d+)", swStr)
    if sw_list:
        if len(sw_list[0]) == 2:
            sysw = float(sw_list[0][0])
            xysw = float(sw_list[0][1])
            swcha_Z = round(sysw - xysw, 2)
            # print(sysw, xysw, swcha_Z)
            if gc_name == "新闸":
                kg_e = round(
                    pow(liuliang / (2.72 * zhakuan * zhakong * swcha_Z**0.5),
                        1 / 1.55), 2)
                # print(kg_e)
                if kg_e <= 2:
                    kg_e = round(
                        pow(
                            liuliang /
                            (3.75 * zhakuan * zhakong * swcha_Z**0.5),
                            1 / 1.07), 2)
                    # return kg_e
            else:
                kg_e = round(
                    pow(liuliang / (xishu * zhakuan * zhakong * swcha_Z**0.5),
                        1 / xishuE), 2)
    result += f"开高：{kg_e}\n"
    return result
    # print(swStr, "流量:", zhaStr_list[0][0], "闸孔:", zhaStr_list[0][1])


def getnewgq(gcnum):
    url = "https://api.u96242.nyat.app:39311/gongkong?gcnum=" + gcnum
    resp = requests.get(url, verify=False)
    sq = ''
    if resp.status_code == 200:
        sq = resp.text
    return sq


def getgq_1zhan():
    result = []
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    sqltxt = "SELECT sysw,xysw,sqsw,ssll,pjll,time FROM swll ORDER BY time DESC LIMIT 1"
    rs = c.execute(sqltxt)
    rf = rs.fetchall()
    if rf:
        result.append(
            time.strftime(r"%y年%m月%d日 %H:%M:%S", time.localtime(rf[0][5])) +
            "\n")
        result.append(f'上游:{rf[0][0]}')
        result.append(f'下游:{rf[0][1]}')
        result.append(f'栅前:{rf[0][2]}')
        result.append(f'瞬时流量:{rf[0][3]}\n')
        # result.append( f'平均流量：{rf[0][4]}')

    for index, ename in enumerate(ename_list):
        sqltxt = f"""SELECT Uab,Ubc,Uca,Ia,Ib,Ic,P,Q,cos,time FROM dianyadianliu WHERE ename="{ename}" ORDER BY time DESC LIMIT 1"""
        rs = c.execute(sqltxt)
        rf = rs.fetchall()
        if rf:
            result.append(realname_list[index])
            result.append(f'Uab:{rf[0][0]}')
            result.append(f'Ubc:{rf[0][1]}')
            result.append(f'Uca:{rf[0][2]}')
            result.append(f'Ia:{rf[0][3]}')
            result.append(f'Ib:{rf[0][4]}')
            result.append(f'Ic:{rf[0][5]}')
            result.append(f'P:{rf[0][6]}')
            result.append(f'Q:{rf[0][7]}')
            result.append(f'cos:{rf[0][8]}\n')
            # result.append(f'time:{rf[0][9]}')
    # result.append(time.strftime(r"%y年%m月%d日 %H:%M:%S",time.localtime(rf[0][9]))+"\n")
    c.close()
    conn.close()
    return ' '.join(result)


def getgq_2zhan():
    result = []
    conn = sqlite3.connect(db2_path)
    c = conn.cursor()
    sqltxt = "SELECT sysw,sqsw,shsw,yc,ldsw,zbwd1,zbwd2,yxts,zll, time FROM qzcs ORDER BY time DESC LIMIT 1"
    rs = c.execute(sqltxt)
    rf = rs.fetchall()
    if rf:
        result.append(
            time.strftime(r"%y年%m月%d日 %H:%M:%S", time.localtime(rf[0][9])) +
            "\n")
        result.append(f'上游: {rf[0][0]}')
        result.append(f'栅前: {rf[0][1]}')
        result.append(f'栅后: {rf[0][2]}')
        result.append(f'扬程: {rf[0][3]}')
        result.append(f'廊道: {rf[0][4]}')
        result.append(f'主变温度1: {rf[0][5]} ℃')
        result.append(f'主变温度2: {rf[0][6]} ℃')
        result.append(f'运行台数: {rf[0][7]}')
        result.append(f'总流量: {rf[0][8]}\n')
        # result.append( f'平均流量：{rf[0][4]}')
    sqltxt = "SELECT * FROM dydlpqcos WHERE time=(SELECT time FROM dydlpqcos WHERE Ia<>0 ORDER BY id DESC LIMIT 1) and Ia<>0"
    rs = c.execute(sqltxt)
    rf = rs.fetchall()
    if rf:
        for r in rf:
            result.append(realname_list2[r[1]])
            result.append(f'Ia:{r[2]}')
            result.append(f'Ic:{r[3]}')
            result.append(f'Uab:{r[4]}')
            result.append(f'Ubc:{r[5]}')
            result.append(f'Uca:{r[6]}')
            result.append(f'P:{r[7]}')
            result.append(f'Q:{r[8]}')
            if r[10]:
                result.append(f'cosφ:{r[9]}')
                result.append(f'lc1:{r[10]}')
                result.append(f'lc2:{r[11]}\n')
            else:
                result.append(f'cosφ:{r[9]}\n')
    c.close()
    conn.close()
    return ' '.join(result)
    # result.append(realname_list2[r[1]], r[2])
    # for index, ename in enumerate(ename_list):
    #     sqltxt = f"""SELECT Uab,Ubc,Uca,Ia,Ib,Ic,P,Q,cos,time FROM dianyadianliu WHERE ename="{ename}" ORDER BY time DESC LIMIT 1"""
    #     rs = c.execute(sqltxt)
    #     rf = rs.fetchall()
    #     if rf:
    #         result.append(realname_list[index])
    #         result.append(f'Uab:{rf[0][0]}')
    #         result.append(f'Ubc:{rf[0][1]}')
    #         result.append(f'Uca:{rf[0][2]}')
    #         result.append(f'Ia:{rf[0][3]}')
    #         result.append(f'Ib:{rf[0][4]}')
    #         result.append(f'Ic:{rf[0][5]}')
    #         result.append(f'P:{rf[0][6]}')
    #         result.append(f'Q:{rf[0][7]}')
    #         result.append(f'cos:{rf[0][8]}\n')
    # result.append(f'time:{rf[0][9]}')
    # result.append(time.strftime(r"%y年%m月%d日 %H:%M:%S",time.localtime(rf[0][9]))+"\n")

    # return ' '.join(result)


def getnysw(num):
    conn = sqlite3.connect(dbsw_path)
    c = conn.cursor()
    result = []
    if num == 0:
        sqltxt = f"""SELECT sysw,xysw,time FROM swdb where name like "刘老涧抽水站" ORDER BY time DESC LIMIT 24 """
        rs = c.execute(sqltxt)
        rf = rs.fetchall()
        if rf:
            # print(rf[0])
            for r in rf:
                result.append(str(r) + '\n')
    c.close()
    conn.close()
    # print(' '.join(result))
    return ' '.join(result)


if __name__ == "__main__":
    # print(getgq)
    # print(getnewsq("刘涧"))
    # getzhakg
    getnysw(0)
    # print(getnewgq("1"))
    # print((getgq_2zhan()))
